package com.boco.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.boco.entity.EMR_ZYYZ;

public class GetEMR_ZYYZData {
	
	private static final Log log = LogFactory.getLog(GetEMR_ZYYZData.class);
	public static List<EMR_ZYYZ> getData()
	{
	    Connection con = null;
	    PreparedStatement pre = null;
	    ResultSet result = null;
	    List<EMR_ZYYZ> re = new ArrayList<EMR_ZYYZ>();
	    try
	    {
	        Class.forName("oracle.jdbc.driver.OracleDriver");
	        System.out.println("开始尝试连接数据库！");
	        String url = "jdbc:oracle:" + "thin:@172.20.21.12:1521:orcl";
	        String user = "sjzx_hxk";
	        String password = "boco#1234";
	        con = DriverManager.getConnection(url, user, password);
	        System.out.println("连接成功！");
	        
	        String sql = " select "
	        		+"'' as HZZSYH,"
					+"a.yljgdm as YLJGZZJGDM,"
					 +"'' as YLJGMC,"
					+"a.jzlsh as ZYH,"
					+"a.xm as XM,"
					+"a.xb as XB,"
					 +"'' as NL,"
					 +"'' as TZ,"
					+"a.bq as BQMC,"
					+"a.xdksbm as KSDM,"
					+"a.xdksmc as KSMC,"
					+"b.bfh as BFH,"
					+"b.bch as BCH,"
					+"a.yzmxid as YZBH,"
					 +"'' as YZXH,"
					 +"'' as XMBM,"
					 +"'' as XMMC,"
					 +"'' as DZSQDBH,"
					 +"'' as SFXM,"
					+"to_char(a.yzxdsj,'YYYY-MM-dd HH24:mi:ss') as YZKLRQSJ,"
					+"a.xdrgh as YZKLZGH,"
					+"a.xdrxm as YZKLZQM,"
					+"a.xdksbm as YZKLKSDM,"
					+"a.xdksmc as YZKLKS,"
					 +"'' as YZJHKSSJ,"
					 +"'' as YZJHJSSJ,"
					 +"'' as YZXMLX,"
					+"a.yzsm as YZBZXX,"
					 +"'' as YZSHRGH,"
					 +"'' as YZSHRQM,"
					 +"'1990-01-01 00:00:00' as SHRQSJ,"
					 +"'' as HDHSGH,"
					 +"'' as HDHSQM,"
					 +"'' as HSHDSJ,"
					+"to_char(a.yzzxsj,'YYYY-MM-dd HH24:mi:ss') as YZZXRQSJ,"
					+"a.zxrgh as ZXYZZGH,"
					+"a.zxrxm as ZXYZZQM,"
					+"a.zxksbm as YZZXKSDM,"
					+"a.zxksmc as YZZXKS,"
					 +"'' as YZZXZT,"
					+"to_char(a.yzzzsj,'YYYY-MM-dd HH24:mi:ss') as YZTZRQSJ,"
					+"a.zzrgh as TZYZZGH,"
					+"a.zzrxm as TZYZZQM,"
					 +"'' as YZSJSX,"
					 +"'' as USE_WAY,"
					 +"'' as USE_WAY_NAME,"
					 +"'' as YZPL,"
					 +"'' as YZPL_CS,"
					 +"'' as YZPL_JE,"
					 +"'' as YZPL_JEDW,"
					 +"'' as DURATION,"
					 +"'' as DURATION_UNIT,"
					 +"'' as TRANSFUSION_SPEED,"
					 +"'' as TRANSFUSION_UNIT,"
					+"a.jl as SINGLE_DOSE,"
					+"a.dw as SINGLE_DOSE_UNIT,"
					
										
					+"'' as ZRLSH,"
					+"to_char(a.ywscsj,'YYYY-MM-dd HH24:mi:ss') as BCRQ,"
					
					+"'' as ORGCODE,"
					+"'' as AREACODE,"
					+"to_char(sysdate,'yyyyMMddHH24miss') as UPLOADTIME,"
					+"'' as  SOURCEID "
					+" from TB_ZY_ZYYZMX a left join "
					+" TB_RYJL b on a.yljgdm = b.yljgdm"
					+" and a.kh = b.kh and a.klx = b.klx  where rownum < 100";
	 
	        log.info("查询sql："+sql);
	        pre = con.prepareStatement(sql);
	        result = pre.executeQuery();
	        System.out.println("执行成功");
	        while (result.next()){
//	            System.out.println(result.getInt("YYDAH"));
	        	EMR_ZYYZ temp = new EMR_ZYYZ();
	        	temp.setHZZSYH(result.getString("HZZSYH"));
	        	temp.setYLJGZZJGDM(result.getString("YLJGZZJGDM"));
	        	temp.setYLJGMC(result.getString("YLJGMC"));
	        	temp.setZYH(result.getString("ZYH"));
	        	temp.setXM(result.getString("XM"));
	        	temp.setXB(result.getString("XB"));
	        	temp.setNL(result.getString("NL"));
	        	temp.setTZ(result.getString("TZ"));
	        	temp.setBQMC(result.getString("BQMC"));
	        	temp.setKSDM(result.getString("KSDM"));
	        	temp.setKSMC(result.getString("KSMC"));
	        	temp.setBFH(result.getString("BFH"));
	        	temp.setBCH(result.getString("BCH"));
	        	temp.setYZBH(result.getString("YZBH"));
	        	temp.setYZXH(result.getString("YZXH"));
	        	temp.setXMBM(result.getString("XMBM"));
	        	temp.setXMMC(result.getString("XMMC"));
	        	temp.setDZSQDBH(result.getString("DZSQDBH"));
	        	temp.setSFXM(result.getString("SFXM"));
	        	temp.setYZKLRQSJ(result.getString("YZKLRQSJ"));
	        	temp.setYZKLZGH(result.getString("YZKLZGH"));
	        	temp.setYZKLZQM(result.getString("YZKLZQM"));
	        	temp.setYZKLKSDM(result.getString("YZKLKSDM"));
	        	temp.setYZKLKS(result.getString("YZKLKS"));
	        	temp.setYZJHKSSJ(result.getString("YZJHKSSJ"));
	        	temp.setYZJHJSSJ(result.getString("YZJHJSSJ"));
	        	temp.setYZXMLX(result.getString("YZXMLX"));
	        	temp.setYZBZXX(result.getString("YZBZXX"));
	        	temp.setYZSHRGH(result.getString("YZSHRGH"));
	        	temp.setYZSHRQM(result.getString("YZSHRQM"));
	        	temp.setSHRQSJ(result.getString("SHRQSJ"));
	        	temp.setHDHSGH(result.getString("HDHSGH"));
	        	temp.setHDHSQM(result.getString("HDHSQM"));
	        	temp.setHSHDSJ(result.getString("HSHDSJ"));
	        	temp.setYZZXRQSJ(result.getString("YZZXRQSJ"));
	        	temp.setZXYZZGH(result.getString("ZXYZZGH"));
	        	temp.setZXYZZQM(result.getString("ZXYZZQM"));
	        	temp.setYZZXKSDM(result.getString("YZZXKSDM"));
	        	
	        	temp.setYZZXKS(result.getString("YZZXKS"));
	        	temp.setYZZXZT(result.getString("YZZXZT"));
	        	temp.setYZTZRQSJ(result.getString("YZTZRQSJ"));
	        	temp.setTZYZZGH(result.getString("TZYZZGH"));
	        	temp.setTZYZZQM(result.getString("TZYZZQM"));
	        	temp.setYZSJSX(result.getString("YZSJSX"));
	        	temp.setUSE_WAY(result.getString("USE_WAY"));
	        	temp.setUSE_WAY_NAME(result.getString("USE_WAY_NAME"));
	        	temp.setYZPL(result.getString("YZPL"));
	        	temp.setYZPL_CS(result.getString("YZPL_CS"));
	        	temp.setYZPL_JE(result.getString("YZPL_JE"));
	        	temp.setYZPL_JEDW(result.getString("YZPL_JEDW"));
	        	temp.setDURATION(result.getString("DURATION"));
	        	temp.setDURATION_UNIT(result.getString("DURATION_UNIT"));
	        	temp.setTRANSFUSION_SPEED(result.getString("TRANSFUSION_SPEED"));
	        	temp.setTRANSFUSION_UNIT(result.getString("TRANSFUSION_UNIT"));
	        	temp.setSINGLE_DOSE(result.getString("SINGLE_DOSE"));
	        	temp.setSINGLE_DOSE_UNIT(result.getString("SINGLE_DOSE_UNIT"));
	        	
	        	temp.setZRLSH(result.getString("ZRLSH"));
	        	temp.setBCRQ(result.getString("BCRQ"));
	        	temp.setORGCODE(result.getString("ORGCODE"));
	        	temp.setAREACODE(result.getString("AREACODE"));
	        	temp.setUPLOADTIME(result.getString("UPLOADTIME"));
	        	temp.setSOURCEID(result.getString("SOURCEID"));
	        	re.add(temp);
	        }
	    }
	    catch (Exception e)
	    {
	        e.printStackTrace();
	    }
	    finally
	    {
	        try
	        {
	            if (result != null)
	                result.close();
	            if (pre != null)
	                pre.close();
	            if (con != null)
	                con.close();
	            System.out.println("数据库连接已关闭！");
	        }
	        catch (Exception e)
	        {
	            e.printStackTrace();
	        }
	    }
		return re;
	}
}
